IF OBJECT_ID(N'dbo.GetArraNotificationDateByPIQuestionnaireId', N'FN') IS NOT NULL
BEGIN
DROP FUNCTION dbo.GetArraNotificationDateByPIQuestionnaireId
END
GO
/****** Object:  UserDefinedFunction [dbo].[GetArraNotificationDateByPIQuestionnaireId]    Script Date: 09/16/2009 12:39:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************
*  Name:  GetArraNotificationDateByPIQuestionnaireId
* Purpose:  Function to get the Last Notification Date.
	This function is called from GetPIQuestionnairesByAdmPersonId stored Proc.
*
* PARAMETERS
* Name    			Description					
* -------------  	-------------------------------------------
* @PIQuestionnaireId     PIQuestionnaire Id
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------- 
* EventDate			EventDate from ArraEventHistory for an event 
*					of type Notification.
******************************************************************/

CREATE FUNCTION [dbo].[GetArraNotificationDateByPIQuestionnaireId](
	@PIQuestionnaireId INTEGER
)
	RETURNS DATETIME
AS
BEGIN

	DECLARE @returnValue DATETIME
	

	SELECT TOP 1 @returnValue =  apiqeh.EventDate
	FROM 
		ArraPIQuestionnaire apiq 
		INNER JOIN ArraPIQuestionnaireEventHistory apiqeh ON apiqeh.ArraPIQuestionnaireId = apiq.Id
		INNER JOIN ArraPIQuestionnaireEvent apiqe ON apiqe.Id = apiqeh.ArraPIQuestionnaireEventId 
		INNER JOIN ArraPIQuestionnaireEventType apiqet ON apiqet.Id = apiqe.ArraPIQuestionnaireEventTypeID
	WHERE
		apiqe.ArraPIQuestionnaireEventTypeID = 1--Notification Event Type
		AND 
		apiq.Id = @PIQuestionnaireId
	ORDER BY apiqeh.EventDate DESC

	RETURN @returnValue

END


--SELECT dbo.GetArraNotificationDateByPIQuestionnaireId(1)
